package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.User;
import com.hnl.util.FormatUtil;

public class DBUserDAO implements UserDAO {
	public static final Logger LOG = LoggerFactory.getLogger(DBUserDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement loadAllStmt;
	private PreparedStatement updateStmt;

	protected DBUserDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	public User saveUser(User user) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO USER (first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user) "
									+ "VALUES (?,?,?,?,?,?,?,?,?,?,?)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setString(1, user.getFirstname());
			saveStmt.setString(2, user.getLastname());
			saveStmt.setString(3, user.getEmail());
			saveStmt.setString(4, user.getPassword());
			saveStmt.setString(5,
					FormatUtil.getXmlDate(user.getLastLoginDate()));
			saveStmt.setInt(6, user.getRoleID() == null ? Integer.valueOf(1)
					: user.getRoleID());
			saveStmt.setBoolean(7, user.getIsDelete());
			saveStmt.setString(8, FormatUtil.getXmlDate(user.getCreateDate()));
			saveStmt.setInt(
					9,
					user.getCreateUserID() == null ? Integer.valueOf(1) : user
							.getCreateUserID());
			saveStmt.setString(10, FormatUtil.getXmlDate(user.getModifyDate()));
			saveStmt.setInt(
					11,
					user.getModifyUserID() == null ? Integer.valueOf(1) : user
							.getModifyUserID());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				user.setUserID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("Error saving User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return user;
	}

	public User loadUserByUserID(Integer id) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		User user = null;
		try {
			loadStmt = conn
					.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
							+ "WHERE user_ID = ?");
			loadStmt.setInt(1, id);
			ResultSet rs = loadStmt.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setUserID(rs.getInt("user_ID"));
				user.setFirstname(rs.getString("first_name"));
				user.setLastname(rs.getString("last_name"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setLastLoginDate(rs.getDate("last_login_date"));
				user.setRoleID(rs.getInt("role_ID"));
				user.setIsDelete(rs.getBoolean("isdelete"));
				user.setCreateDate(rs.getDate("create_date"));
				user.setCreateUserID(rs.getInt("create_user"));
				user.setModifyDate(rs.getDate("modify_date"));
				user.setModifyUserID(rs.getInt("modify_user"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return user;
	}

	public void deleteUser(Integer userID) {
		User user = loadUserByUserID(userID);
		user.setIsDelete(true);
		updateUser(user);
	}

	public void updateUser(User user) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		try {
			updateStmt = conn
					.prepareStatement("UPDATE USER SET first_name=?, last_name=?, email=?, password=?, last_login_date=?, role_ID=?, isdelete=?, create_date=?, create_user=?, modify_date=?, modify_user=?"
							+ " WHERE user_ID=?");
			updateStmt.setString(1, user.getFirstname());
			updateStmt.setString(2, user.getLastname());
			updateStmt.setString(3, user.getEmail());
			updateStmt.setString(4, user.getPassword());
			updateStmt.setString(5,
					FormatUtil.getXmlDate(user.getLastLoginDate()));
			updateStmt.setInt(6, user.getRoleID() == null ? Integer.valueOf(1)
					: user.getRoleID());
			updateStmt.setBoolean(7, user.getIsDelete());
			updateStmt
					.setString(8, FormatUtil.getXmlDate(user.getCreateDate()));
			updateStmt.setInt(
					9,
					user.getCreateUserID() == null ? Integer.valueOf(1) : user
							.getCreateUserID());
			updateStmt.setString(10,
					FormatUtil.getXmlDate(user.getModifyDate()));
			updateStmt.setInt(
					11,
					user.getModifyUserID() == null ? Integer.valueOf(1) : user
							.getModifyUserID());
			updateStmt.setInt(12, user.getUserID() == null ? Integer.valueOf(1)
					: user.getUserID());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("Error updating User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	public Collection<User> loadAllUser() {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<User> userCollection = new Vector<User>();
		try {
			loadAllStmt = conn
					.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
							+ "WHERE isdelete=false");
			ResultSet rs = loadAllStmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUserID(rs.getInt("user_ID"));
				user.setFirstname(rs.getString("first_name"));
				user.setLastname(rs.getString("last_name"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setLastLoginDate(rs.getDate("last_login_date"));
				user.setRoleID(rs.getInt("role_ID"));
				user.setIsDelete(rs.getBoolean("isdelete"));
				user.setCreateDate(rs.getDate("create_date"));
				user.setCreateUserID(rs.getInt("create_user"));
				user.setModifyDate(rs.getDate("modify_date"));
				user.setModifyUserID(rs.getInt("modify_user"));
				userCollection.add(user);
			}
			rs.close();
			loadAllStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return userCollection;
	}

	public Collection<User> loadUserByUserEmail(String userEmail) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		Collection<User> userCollection = new Vector<User>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
							+ "WHERE isdelete=false AND email like ?");
			loadStmt.setString(1, "%" + userEmail + "%");
			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUserID(rs.getInt("user_ID"));
				user.setFirstname(rs.getString("first_name"));
				user.setLastname(rs.getString("last_name"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setLastLoginDate(rs.getDate("last_login_date"));
				user.setRoleID(rs.getInt("role_ID"));
				user.setIsDelete(rs.getBoolean("isdelete"));
				user.setCreateDate(rs.getDate("create_date"));
				user.setCreateUserID(rs.getInt("create_user"));
				user.setModifyDate(rs.getDate("modify_date"));
				user.setModifyUserID(rs.getInt("modify_user"));
				userCollection.add(user);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return userCollection;
	}

	public User loadUserByUsername(String username) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();

		User user = null;
		try {
			loadStmt = conn
					.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
							+ "WHERE isdelete=false AND email = ?");
			loadStmt.setString(1, username.toString());
			ResultSet rs = loadStmt.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setUserID(rs.getInt("user_ID"));
				user.setFirstname(rs.getString("first_name"));
				user.setLastname(rs.getString("last_name"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setLastLoginDate(rs.getDate("last_login_date"));
				user.setRoleID(rs.getInt("role_ID"));
				user.setIsDelete(rs.getBoolean("isdelete"));
				user.setCreateDate(rs.getDate("create_date"));
				user.setCreateUserID(rs.getInt("create_user"));
				user.setModifyDate(rs.getDate("modify_date"));
				user.setModifyUserID(rs.getInt("modify_user"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return user;
	}

	public boolean CheckDuplicateEmail(String userEmail, String userID) {
		boolean isDplicate = false; // true:existed email, false:no existed
									// email
		int rowcount = 0;
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			if (userID == "" || userID == null || userID == "0") {
				loadStmt = conn
						.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
								+ "WHERE isdelete=false AND email = ?");
				loadStmt.setString(1, userEmail);
			} else {
				loadStmt = conn
						.prepareStatement("SELECT user_ID, first_name, last_name, email, password, last_login_date, role_ID, isdelete, create_date, create_user, modify_date, modify_user FROM USER "
								+ "WHERE isdelete=false AND email = ? AND user_ID != ?");
				loadStmt.setString(1, userEmail);
				loadStmt.setString(2, userID);
			}

			ResultSet rs = loadStmt.executeQuery();
			while (rs.next()) {
				rowcount++;
			}

			if (rowcount > 0) {
				isDplicate = true;
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("Error loading User.", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}

		return isDplicate;
	}

}
